import pandas as pd
import pymysql
from sqlalchemy import create_engine
Tuser = 'root'
Tpassword = 'tjx890612'
Thost = 'localhost'
Tdatabase = 'robot_ansys'
Tport =3306
column_name =['姓名','年龄']
type_name = ['varchar(255)', 'varchar(255)']

class mysqlIO(object):
    def __init__(self, Tuser, Thost,Tport,Tpassword):
        # self.conn = pymysql.connect(user=Tuser,host=Thost,port=3306,
        #                             passwd=Tpassword,database=Tdatabase)  # 连接主机
        # self.cur = self.conn.cursor()
        self.conn = pymysql.connect(
            host=Thost,
            user=Tuser,
            passwd=Tpassword,
            port=Tport
            #db = "dome
        )
        self.cur = self.conn.cursor()
        
    def dbconnect(self,Tdatabase):
        self.conn.select_db(Tdatabase)
        # sql = 'Create database %s'
        
    #创建表
    def Table_create(self,table_name,column_name,type_name):
        temp = 'CREATE TABLE if not exists '+table_name+' ('
        icount = len(column_name)
        for i in range(0,icount-1):
            temp = temp +column_name[i]+' '+type_name[i]+','
        temp = temp+column_name[icount-1]+' '+type_name[i]+')'
        print(temp)
        self.cur.execute(temp)
    #删除表
    def drop_table(self,table_name):
        sql = 'DROP TABLE '+table_name
        self.cur.execute(sql)
        
    #改变列属性
    def alter_column_type(self,table_name,column_name,datatype):
        sql = 'ALTER TABLE {} MODIFY COLUMN {} {}'.format(table_name,column_name,datatype)
        self.cur.execute(sql)
        
    #获取数据库表格
    def get_table_name(self):
        self.cur.execute('SHOW TABLES')
        for i in self.cur.fetchall():
            print(i[0])
    
    #获取表格字段列表
    def get_table_column(self,table_name):
        table_column =[]
        sql = 'select * from '+table_name
        result = self.cur.execute(sql)  # 默认不返回查询结果集， 返回数据记录数。

        # 显示每列的详细信息
        des = self.cur.description
        # print("表的描述:", des)

        # 获取表头
        # print("表头:", ",".join([item[0] for item in des]))
        for item in des:
            table_column.append(item[0])
        return table_column
    #添加数据
    def add_data(self,table_name,data_add):
        table_column = self.get_table_column(table_name)
        temp = ' ('
        for i in range(0,len(table_column)-1):
            temp = temp+table_column[i]+','
        temp = temp+table_column[len(table_column)-1]+')'
        sql = 'insert into '+table_name+temp+' values '+str(data_add)+';'
        
        # self.cur.execute(sql)
        try:
            # 执行sql语句
            q = self.cur.execute(sql)
            # 提交到数据库执行
            self.conn.commit()
            print(sql)
        except:
        # 发生错误时回滚
            self.conn.rollback()
        
    # 执行查询
    def search_data(self,sql):
        s_data = []
        self.cur.execute(sql)
        results = self.cur.fetchall()
        for row in results:
            s_data.append(row)
        return s_data
    #断开连接
    
    def disconnect(self):
        self.cur.close()
        self.conn.close()
        
class pd_sql(object):
    def __init__(self, Tuser, Thost, Tport, Tpassword,Tdatabase):
        sql = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(
            Tuser, Tpassword, Thost, Tport, Tdatabase)
        self.engine = create_engine(sql) # 创建引擎
        print('已连接数据库 {}'.format(Tdatabase))
    
    #导出表格数据到pandas数据集
    def get_data(self,table_name):
        sql = 'select * from {}'.format(table_name)
        df = pd.read_sql_query(sql,self.engine)
        return df
    
    #将pandas数据集保存到数据库表table_name中
    def save_data(self,table_name,df):
        #DataFrame储存为MySQL中的数据表，不储存index列,如果存在则追加数据
        df.to_sql(table_name, self.engine, if_exists='append',index=False)
        print('Read from and write data to database table %s successfully!', table_name)
        
    #断开数据库连接
    def disconnect(self):
        self.engine.dispose()
        print('数据库连接已断开！')

        
        
    
if __name__ == '__main__':
    # myconnect = mysqlIO(Tuser, Thost, Tport, Tpassword)
    # myconnect.dbconnect(Tdatabase)
    # myconnect.get_table_name()
    # j = myconnect.get_table_column('巡检结果表')
    # print(j)
    # # myconnect.Table_create('名单3',column_name,type_name)
    # # myconnect.drop_table('名单2')
    # # sql = 'select * from 操作票统计'
    # # k = []
    # # k = myconnect.search_data(sql)
    # # print(k)
    # sql = 'select * from 名单3'
    # # myconnect.alter_column_type('名单3','年龄','int')
    # # xm = ('lihao','hj')
    # # myconnect.add_data('名单3',xm)
    # # k=myconnect.search_data(sql)
    # # print(k)
    # k = input()
    # myconnect.disconnect()
    df = pd.DataFrame()
    pdconnect = pd_sql(Tuser, Thost, Tport, Tpassword, Tdatabase)
    df = pdconnect.get_data('巡检结果表')
    pdconnect.disconnect()
    print(df.info())
    print(df.head(5))
    df.to_csv('巡检结果表.csv',index = False)
        
